<?php
include_once("../includes/page_utf_class.php");
include_once("../module/product/includes/plugin_order_class.php");
include("../lang/cn/company_type_config.php");
include_once WEBROOT.'/module/inventory/config.inc.php';
$inventory_config = new config();
$order = new order;
//-------导入运单号-------
if($_GET['act']=='importexpress'){
	$tpl->display("uploadFile.htm");
	exit;
}
//--------------------

//----------------------分仓
$general_shop =$order->get_general_shop();

if ($general_shop!=0&&$general_shop!=$_GET['stock_shop']&&isset($_GET['stock_shop'])){
	msg('noright.php');
	exit;
}
if ($general_shop){
	$_GET['stock_shop'] =$general_shop;
}
if ($_GET['stock_shop']){
	$str.=" and stock_shop = '$_GET[stock_shop]' ";
}
//-----------------

if($_GET['code'])
{
	$str=" and  a.order_id like '$_GET[code]%' ";
}
if($_GET['express_print_status']!=""){
	$str.=" and a.express_print_status='{$_GET['express_print_status']}'";
}else{
	if(isset($_GET['express_print_status']) && $_GET['express_print_status']==""){
		
	}else{
		$_GET['express_print_status'] = '0';
		$str.=" and a.express_print_status='0'";
	}
}
if($_GET['express_export_status']!=""){
	$str.=" and a.express_export_status='{$_GET['express_export_status']}'";
}
if($_GET['status']!=""){
	$str.=" and a.status = '{$_GET['status']}' ";
}else{
	$str.=" and  a.status >=2 ";//待发货和已发货订单
}
if($_GET['s_stime'])
{
	$str.=" and IFNULL(ship_time,0) > '".strtotime($_GET[s_stime])."' ";
}
if($_GET['s_etime'])
{
	$str.=" and IFNULL(ship_time,0) <= '".strtotime($_GET[s_etime])."' ";
}
if($_GET['p_stime'])
{
	$str.=" and IFNULL(express_print_time,0) > '".strtotime($_GET[p_stime])."' ";
}
if($_GET['p_etime'])
{
	$str.=" and IFNULL(express_print_time,0) <= '".strtotime($_GET[p_etime])."' ";
}
if($_GET['shipping_no']){
    $shipping_no = trim($_GET['shipping_no']);
    $str.=" and a.shipping_no in({$shipping_no}) ";
}

//查询邮政小包快递ID
$sqle = "select id from ".FASTMAIL." where sign='sps' limit 1";
$db->query($sqle);
$expressId = $db->fetchField('id');
$str.=" and a.shipping_express_id={$expressId}";
// $sql="select * from ".ORDER."  where status=2 $str order by id desc";
$exportSql = $sql="select a.member_id,a.status,a.print_status,a.order_id,a.client_system,a.client_platform,
		(select 1 from ".$inventory_config->db_prefix."orders where lotnumber =a.order_id and type=0 and timestocked=0 limit 1) is_stocked,
				ship_name,ship_mobile,ship_addr,shipping_idcard,shipping_weight,shipping_freight,order_time,pay_time,ship_time,
				finished_time,pay_status,ship_status,shipping_name,payment_name,shipping_no,express_print_status,tax_price,
				express_export_status,express_print_time,stock_shop,shipping_express_name,order_type from ".ORDER." a  
where 1 $str having is_stocked is NULL order by a.express_print_time,a.order_time ";
//=============================
$page = new Page;
$page->listRows=20;
if (!$page->__get('totalRows')){
	$db->query($sql);
	$de['count']=$page->totalRows = $db->num_rows();
}
$de['count']=$de['count']?$de['count']:$_GET['totalRows'];
$sql .= "  limit ".$page->firstRow.",".$page->listRows;
$de['page'] = $page->prompt();
//=============================
$db->query($sql);
$ss=$db->getRows();
foreach($ss as $k)
{
	$k['order_ship_status']=$order_ship_status[$k['status']-1];
	$k['shipping_freight_rmb'] = $k['shipping_freight']*$price_rate_config['euro'];
	$list[]=$k;
}
$de["list"]=$list;

//----------------------分仓
$stock_shops=$order->get_stock_shops();
$tpl->assign("stock_shops",$stock_shops);
$tpl->assign("general_shop",$general_shop);
//-----------------------------

foreach ($stock_shops as $sv)
{
	$stockShopInfo[$sv['id']] = $sv['title'];
}
if($_POST['act']=='export_oid_eid'){//导出订单号和运单号
    include_once '../lib/php_excel.php';
    $db->query($exportSql);
    $export_data_temp=$db->getRows();
    foreach ($export_data_temp as $key=> $var)
    {
        $export_data[$key][] = $var['order_id'];//订单号
        $export_data[$key][] = $var['shipping_no'];//运单号
    }
    $queue[0] = array("订单号","运单号");//定义表头
    $export_data = array_merge($queue,$export_data);
    
    $filename =   "订单号和运单号" . date ( "YmdHis" );
    $xls = new Excel_XML ( 'UTF-8', false, '订单号和运单号' );
    $xls->addArray ( $export_data );
    $xls->generateXML ( $filename );
    exit ();
}
if($_POST['act']=="export"){//导出订单信息（为快递公司）
	include_once '../lib/php_excel.php';
	$db->query($exportSql);
	$export_data_temp=$db->getRows();
	$orderIds = "";
	foreach ($export_data_temp as $key=> $var)
	{
		$orderIds .= $var['order_id'].",";
		$export_data[$key][] = "";//邮件号
		$export_data[$key][] = $var['order_id'];//配货单号,订单号
		$export_data[$key][] = "";//客户订单号
		$export_data[$key][] = $company_information[$var['stock_shop']]['name'];//寄件人姓名
		$export_data[$key][] = $company_information[$var['stock_shop']]['tel'];//寄件人联系方式
		$export_data[$key][] = "";//寄件人联系方式（2）
		$export_data[$key][] = $company_information[$var['stock_shop']]['addr'];//寄件人地址
		$export_data[$key][] = "";//寄件人公司
		$export_data[$key][] = "";//寄件省
		$export_data[$key][] = "";//寄件市
		$export_data[$key][] = "";//寄件县
		$export_data[$key][] = "";//寄件人邮编
		$export_data[$key][] = $var['ship_name'];//收件人姓名
		$export_data[$key][] = $var['ship_mobile'];//收件人联系方式
		$export_data[$key][] = "";//收件人联系方式（2）
		$export_data[$key][] = $var['ship_addr'];//收件人地址
		$export_data[$key][] = "";//收件人公司
		$export_data[$key][] = "";//到件省/直辖市
		$export_data[$key][] = "";//到件城市
		$export_data[$key][] = "";//到件县/区
		$export_data[$key][] = "";//收件人邮编
		$export_data[$key][] = $var['shipping_weight'];//物品重量
		$export_data[$key][] = "";//物品长度
		$export_data[$key][] = "";//物品宽度
		$export_data[$key][] = "";//物品高度
		$export_data[$key][] = "";//打单时间
		$export_data[$key][] = "";//备注
// 		$export_data[$key][] = (mb_substr($var['ship_addr'], 0, 3, 'utf-8')=='四川省')?"标准快递":"经济快递";//业务类型
		$export_data[$key][] = "快递包裹";//业务类型
		$export_data[$key][] = "";//代收货款
		$export_data[$key][] = "";//收件人付费
		$export_data[$key][] = "";//应收货款/邮资
		$export_data[$key][] = "";//应收货款/邮资（大写）
		$export_data[$key][] = "";//内件性质
		$export_data[$key][] = "";//内件数
		$export_data[$key][] = "";//内件信息
		$export_data[$key][] = "";//留白一
		$export_data[$key][] = $stockShopInfo[$var['stock_shop']];//留白二(仓库名)
		$export_data[$key][] = "";//付费类型
		$export_data[$key][] = "";//所负责任
		$export_data[$key][] = "";//保价金额
		$export_data[$key][] = "";//保险金额
		$export_data[$key][] = "";//其他费
	}
	$queue[0] = array("邮件号","*配货单号", "客户订单号","*寄件人姓名","*寄件人联系方式","寄件人联系方式（2）","*寄件人地址","寄件人公司","寄件省","寄件市","寄件县","寄件人邮编","*收件人姓名","*收件人联系方式","收件人联系方式（2）","*收件人地址","收件人公司","到件省/直辖市","到件城市","到件县/区","收件人邮编","物品重量","物品长度","物品宽度","物品高度","打单时间","备注","业务类型","代收货款","收件人付费","应收货款/邮资","应收货款/邮资（大写）","内件性质","内件数","内件信息","留白一","留白二","付费类型","所负责任","保价金额","保险金额","其他费");//定义表头
	$export_data = array_merge($queue,$export_data);
	if($orderIds!=''){
		//更新导出状态为已导出
		$orderIds = rtrim($orderIds,",");
		$sql = "update ".ORDER." set express_export_status=1 where order_id in({$orderIds})";
		$db->query($sql);
	}
	//print_r($export_data);
	$filename =   "订单信息" . date ( "YmdHis" );
	$xls = new Excel_XML ( 'UTF-8', false, '订单信息' );
	$xls->addArray ( $export_data );
	$xls->generateXML ( $filename );
	exit ();
}
if($_POST['act']=='export_customs'){//导出海关申报数据
	include_once '../lib/php_excel.php';
	$db->query($exportSql);
	$export_data_temp=$db->getRows();
	foreach ($export_data_temp as $kk=>$vv)
	{
		$products = $order->get_order_express_pro($vv['order_id'],$sum,$total_num);
		foreach ($products as $pk=>$pv)
		{
			$export_data[] = array(//新申报模板
					($pk==0)?$kk+1:'',//序号
					($pk==0)?'':'',//总运单号
					($pk==0)?$vv['shipping_no']:'',//分单号
					($pk==0)?'I30':'',//业务类型代码
					($pk==0)?'':'',//航次号
					($pk==0)?'I':'',//进出口标识
					($pk==0)?'7906':'',//进出口岸
					($pk==0)?'5':'',//运输方式代码
					($pk==0)?'':'',//运输工具名称
					($pk==0)?$company_information[$vv['stock_shop']]['name'].'（'.$company_information[$vv['stock_shop']]['country_name_cn'].'）':'',//发件人
					($pk==0)?$company_information[$vv['stock_shop']]['country_code']:'',//发件人国别代码
					($pk==0)?$vv['ship_addr']:'',//收件人地址
					($pk==0)?$vv['ship_name']:'',//收件人名称
					($pk==0)?$vv['shipping_idcard']:'',//收件人身份证
					($pk==0)?$vv['ship_mobile']:'',//收件人电话
					($pk==0)?'142':'',//币制代码
					($pk==0)?'':'',//进出口日期
					($pk==0)?'7906':'',//关区代码
					($pk==0)?'':'',//包装件数
					($pk==0)?'':'',//包装种类
					($pk==0)?$vv['shipping_weight']:'',//重量
					($pk==0)?'':'',//备注
					($pk==0)?'':'',//明细序号
					$pv['hscode'],//行邮税号
					$pv['name'],//规格型号
					$pv['num'],//申报数量
					$pv['unit']?$pv['unit']:'个',//申报单位
					$pv['declared_price'],//价值（单价）
					$pv['cat'],//分类
					$pv['brand'],//品牌
				);
			$export_data_master[] = array(//申报总表数据
					($pk==0)?$kk+1:'',//编号
					($pk==0)?$vv['shipping_idcard']:'',//身份证
					($pk==0)?$vv['shipping_no']:'',//分提单号
					($pk==0)?$company_information[$vv['stock_shop']]['country_name_cn']:'',//发件人国家
					($pk==0)?$company_information[$vv['stock_shop']]['name']:'',//寄件人姓名
					($pk==0)?$company_information[$vv['stock_shop']]['tel']:'',//寄件人电话
					($pk==0)?$vv['ship_name']:'',//收件人姓名
					($pk==0)?$vv['ship_mobile']:'',//收件人联系电话
					($pk==0)?$vv['ship_addr']:'',//收件人地址
					$pv['hscode'],//税号
					$pv['name'],//包裹内物品
					$pv['brand'],//品牌
					($pk==0)?$vv['shipping_weight']:'0.0001',//重量(KG)
					($pk==0)?$sum:'',//申报价值（￥）(总申报价值)
					$pv['declared_price']*$pv['num'],//申报价值(¥)(每个商品总申报价值)
					($pk==0)?$total_num:'',//成交数量(商品SKU总数量)
					$pv['num'],//成交数量(订单单个SKU购买数量)
					$pv['unit']?$pv['unit']:'个',//成交单位
					($pk==0)?($sum/$total_num):'',//单价（cny）(总价除以总数量)
					$pv['declared_price'],//单价（cny）(SKU单价)
					1,//分运单件数(件）
			);
			if($pk==0){
				$export_data_airport[] = array(//快件舱单申报表格
						'',//总运单号
						$vv['shipping_no'],//分运单号
						'',//英文货物名称
						$pv['name'],//中文货物名称
						1,//件数
						$vv['shipping_weight'],//重量（公斤）
						$sum,//价值
						142,//币制
						$vv['ship_name'],//收件人
						$company_information[$vv['stock_shop']]['name'],//发件人
						$company_information[$vv['stock_shop']]['country_code'],//发件人国别（地区）
						$company_information[$vv['stock_shop']]['country_name_cn'],//发件人城市
						'',//发件人证件号码
						5,//报关类别
						$vv['order_id'],//订单号
						$vv['tax_price'],//订单行邮税
				);
			}
		}
	}
	

	$filename =   "订单海关申报信息" . date ( "YmdHis" );
	$xls = new Excel_XML ( 'UTF-8', false );
	
	$queue[0] = array("序号","总运单号","分单号","业务类型代码","航次号","进出口标识","进出口岸","运输方式代码","运输工具名称","发件人","发件人国别代码","收件人地址","收件人名称","收件人身份证","收件人电话","币制代码","进出口日期","关区代码","包装件数","包装种类","重量","备注","明细序号","行邮税号","规格型号","申报数量","申报单位","价值（单价）","分类","品牌");//定义表头
	$export_data = array_merge($queue,$export_data);
	$xls->addArray ( $export_data );
	$xls->addSheet ( '订单海关申报信息' );
	
	$queue_master[0] = array("编号","身份证","分提单号","发件人国家","寄件人姓名","寄件人电话","收件人姓名","收件人联系电话","收件人地址","税号","包裹内物品","品牌","重量(KG)","申报价值（￥）","申报价值(¥)","成交数量","成交数量","成交单位","单价（cny）","单价（cny）","分运单件数(件）");
	$export_data_master = array_merge($queue_master,$export_data_master);
	$xls->addArray ( $export_data_master );
	$xls->addSheet ( '订单海关申报总表信息' );
	
	$queue_airport[0] = array("总运单号","分运单号","英文货物名称","中文货物名称","件数","重量（公斤）","价值","币制","收件人","发件人","发件人国别（地区）","发件人城市","发件人证件号码","报关类别","订单号","行邮税");
	$export_data_airport = array_merge($queue_airport,$export_data_airport);
	$xls->addArray ( $export_data_airport );
	$xls->addSheet ( '快件舱单申报信息' );
	
	$xls->generateXML ( $filename, true );
	exit ();
}
//导入运单号
if($_POST['act']=="import" && !empty($_FILES['order_express_no'])){
	require_once '../lib/excel_reader2.php';
	if(!($_FILES['order_express_no']['size']>0)){
		exit('上传文件为空');
	}

	$data = new Spreadsheet_Excel_Reader($_FILES['order_express_no']['tmp_name']);
// 	echo $data->dump(true,true);exit;
	//先找第一行，找出目标字段所在列数
	$order_id_colnum = 0;
	$express_no_colnum = 0;
	for($col=1;$col<=$data->colcount();$col++) {
		$val = $data->val(1,$col);
		if($val=='配货单号'){
			$order_id_colnum = $col;
		}
		if($val=='详情单号'){
			$express_no_colnum = $col;
		}
	}
	//把目标值放入数组
	$expressNoArr = array();
	if($order_id_colnum>0 && $express_no_colnum>0){
		for($row=2;$row<=$data->rowcount();$row++) {
			$expressNoArr[] = array(
					'order_id'	=>$data->val($row,$order_id_colnum),
					'express_no'=>$data->val($row,$express_no_colnum)
			);
		}
	}

	foreach ($expressNoArr as $v)
	{
		if($v['order_id'] && $v['express_no']){
			$sql = "update ".ORDER." set shipping_no='{$v['express_no']}' where order_id={$v['order_id']}";
			$db->query($sql);
			$order->set_order_statu($v['order_id'],3,$_SESSION['ADMIN_USER']);
		}
	}
	echo '<script>window.parent.main.document.location.reload();window.parent.DialogManager.close("importexpress");</script>';
	exit;
}

$tpl->assign("order_shop_status",$order_shop_status);
$tpl->assign("de",$de);
$tpl->assign("config",$config);
$tpl->display("express_paper_print.htm");

?>